﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using CustomORM.Extensions;

namespace CustomORM.DataAccess
{
    /// <summary>
    /// This class is not intended to be used inside of application
    /// You can consider it only as separate implementation or example of IDataAccess
    /// At the moment it is used for Unit Testing and rapid testing of data fetching logic
    /// </summary>
    public class AdoDataAccess : IDataAccess
    {
        protected string ConnectionString { get; set; }
        protected DbProviderFactory Factory { get; set; }
        private DbConnection _connection;
        protected DbConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = CreateAndOpenConnection();
                }
                else if (_connection.State != ConnectionState.Open)
                {
                    _connection.Open();
                }
                return _connection;
            }
            set { _connection = value; }
        }

        public AdoDataAccess(string connectionString, DbProviderFactory factory)
        {
            ConnectionString = connectionString;
            Factory = factory;
            _connection = CreateAndOpenConnection();
        }

        public AdoDataAccess(string connectionString, string providerName)
        {
            ConnectionString = connectionString;
            // If you get this error "The 'DbProviderFactories' section can only appear once per config file."
            // Verify these two files
            // C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\Machine.Config
            // C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\Machine.Config
            // if they don't have <DbProviderFactories> repeated
            Factory = DbProviderFactories.GetFactory(providerName);
            CreateAndOpenConnection();
        }

        private DbConnection CreateAndOpenConnection()
        {
            var connection = Factory.CreateConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();
            return connection;
        }

        public DbTransaction BeginTransaction()
        {
            return Connection.BeginTransaction();
        }

        public DataTable SelectSQL(string selectSql, Dictionary<string, object> parameters = null)
        {
            var dataTable = new DataTable();

            if (Connection != null && Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }

            DbCommand selectCommand = PrepareCommand(Connection, selectSql, parameters);
            var adapter = Factory.CreateDataAdapter();
            adapter.SelectCommand = selectCommand;
            adapter.Fill(dataTable);

            return dataTable;
        }



        private DbCommand PrepareCommand(DbConnection connection, string sqlText, Dictionary<string, object> parameters)
        {
            var selectCommand = Factory.CreateCommand();
            selectCommand.CommandText = sqlText;
            selectCommand.Connection = connection;
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    var parameter = Factory.CreateParameter();
                    parameter.ParameterName = param.Key;
                    parameter.Value = param.Value;

                    if (parameter.Value.GetType() == typeof(string))
                    {
                        //TODO: why is this needed?
                        parameter.Size = 100;
                    }

                    selectCommand.Parameters.Add(parameter);
                }
            }
            return selectCommand;
        }

        public object InsertUpdateRow(RecordData recordData)
        {
            if (recordData.KeyNames.Length != 1)
            {
                throw new Exception("For now only one key column is supported");
            }

            var keyColumnName = recordData.KeyNames.First().ToString();
            var selectSql = GenerateSimpleSelectSQL(recordData, keyColumnName, null);
            object preparedValue = PrepareDbObject(recordData.KeyValues.First());
            var parameters = new Dictionary<string, object>();
            parameters.Add(keyColumnName.Replace(" ", ""), preparedValue);

            var existingData = SelectSQL(selectSql, parameters);
            if (existingData.Rows.Count > 0)
            {
                return UpdateRow(recordData);
            }
            else
            {
                return InsertRow(recordData);
            }
        }

        private object UpdateRow(RecordData recordData)
        {
            var sbSet = new StringBuilder("SET ");
            for (int i = 1; i < recordData.FieldNames.Length; i++)
            {
                sbSet.AppendFormat("[{0}] = @{1}, {2}", recordData.FieldNames[i], recordData.FieldNames[i].ToString().Replace(" ", ""), Environment.NewLine);
            }
            var lengthToRemove = 2 + Environment.NewLine.Length;
            if (sbSet.Length >= lengthToRemove)
            {
                sbSet.Remove(sbSet.Length - lengthToRemove, lengthToRemove);
            }

            string updateSql = string.Format("UPDATE [{0}] {1} WHERE [{2}] = @{3}", recordData.TableName, sbSet.ToString(), recordData.FieldNames[0], recordData.FieldNames[0].ToString().Replace(" ", ""));

            var parameters = PrepareParameters(recordData);

            DbCommand insertCommand = PrepareCommand(Connection, updateSql, parameters);
            insertCommand.ExecuteNonQuery();

            return recordData.FieldValues[0];
        }

        private object InsertRow(RecordData recordData)
        {
            object[] fieldsList;
            if (recordData.KeyFieldAssigned)
            {
                fieldsList = recordData.FieldNames;
            }
            else
            {
                fieldsList = recordData.FieldNames.Skip(1).ToArray();
            }

            var fields = fieldsList.ToCommaSeparatedString("[", "]");
            var fieldParams = fieldsList.ToCommaSeparatedString("@", null, 0, true);
            string insertSql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", recordData.TableName, fields, fieldParams);

            var parameters = PrepareParameters(recordData);
            var identity = ExecuteInsertRow(insertSql, recordData.TableName, parameters);

            // if key value was assigned by us SELECT @@IDENTITY wont get anything good if we are in
            // same connection, previous inserts were executed, thus using assigned value
            if (recordData.KeyFieldAssigned)
            {
                return recordData.KeyValues.First();
            }
            return identity;
        }

        private Dictionary<string, object> PrepareParameters(RecordData recordData)
        {
            var parameters = new Dictionary<string, object>();
            var valuesEnumerator = recordData.FieldValues.GetEnumerator();
            foreach (var field in recordData.FieldNames)
            {
                valuesEnumerator.MoveNext();
                var value = valuesEnumerator.Current;
                parameters.Add(field.ToString().Replace(" ", ""), value);
            }
            return parameters;
        }

        public string GenerateSimpleSelectSQL(RecordData recordData, string columnName, object columnValue)
        {


            return string.Format("SELECT [{0}] FROM [{1}] WHERE [{2}] = @{3}", columnName, recordData.TableName, columnName, columnName.Replace(" ", ""));
        }

        protected static object PrepareDbObject(object value)
        {
            return value ?? DBNull.Value;
        }

        public object InsertUpdateRow(string tableName, object[] fieldsList, object[] fieldsValues, object[] keyFields, object[] keyValues)
        {
            throw new Exception("Use overload method");
            //return InsertRow(tableName, fieldsList, fieldsValues, keyFields, keyValues);

            // TODO: continue working on update
            /*
            bool needsInsert = false;

            var enumerator = fieldsValues.GetEnumerator();
            enumerator.MoveNext();
            var keyValue = enumerator.Current;
            if (keyValue == null || keyValue == DBNull.Value)
            {
                needsInsert = true;
            }
            else if (keyValue.GetType().IsValueType)
            {
                var valueType = keyValue.GetType();
                var defaultInstanceOfType = Activator.CreateInstance(valueType);
                if (defaultInstanceOfType == keyValue)
                {
                    needsInsert = true;
                }
            }
            else if (keyValue.GetType() == typeof(string))
            {
                // THis is not correct, but a good assumption...
                if(string.IsNullOrEmpty(keyValue.ToString()))
                {
                    needsInsert = true;
                }
            }

            
            if (needsInsert)
            {
                identity = InsertRow(tableName, fieldsList, fieldsValues, keyFields);
            }
            else
            {
                identity = UpdateRow(tableName, fieldsList, fieldsValues, keyFields);
            }
            return identity;
            */
        }

        private object ExecuteInsertRow(string insertUpdateSql, string tableName, Dictionary<string, object> parameters)
        {
            DataTable scopeTable = new DataTable();

            DbCommand insertCommand = PrepareCommand(Connection, insertUpdateSql, parameters);
            insertCommand.ExecuteNonQuery();

            var adapter = Factory.CreateDataAdapter();
            var scopeIdentity = "SELECT @@IDENTITY";
            //var scopeIdentity = string.Format("SELECT IDENT_CURRENT('{0}')", tableName);
            adapter.SelectCommand = PrepareCommand(Connection, scopeIdentity, null);
            adapter.Fill(scopeTable);

            var identity = scopeTable.Rows[0][0];

            if (identity == DBNull.Value)
            {
                // most likely key is not identity
                return parameters.Values.First();
            }

            return identity;
        }

        public void DeleteRow(string tableName, IEnumerable fieldsList, IEnumerable fieldsValues, IEnumerable keyFields)
        {
            throw new Exception("This method is not longer valid, instead use: DeleteRow(RecordData recordData)");
        }

        public void DeleteRow(RecordData recordData)
        {
            var keyFieldName = recordData.KeyNames[0];
            var keyFieldValue = recordData.KeyValues[0];
            var keyFieldParam = keyFieldName.ToString().Replace(" ", "");

            string deleteSql = string.Format("DELETE FROM [{0}] WHERE [{1}] = @{2}", recordData.TableName, keyFieldName, keyFieldParam);

            var parameters = new Dictionary<string, object>();
            parameters.Add(keyFieldParam, keyFieldValue);

            DbCommand deleteCommand = PrepareCommand(Connection, deleteSql, parameters);
            deleteCommand.ExecuteNonQuery();
        }
    }
}